﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ERP.Manager.Core
{
    /// <summary>
    /// sql语句规范类；
    /// </summary>
    public class ERPSQL
    {

        /*
         *-----------------------------------------------------------------------------------------------------
         *----------------------------------------用户信息表---------------------------------------------------
         *-----------------------------------------------------------------------------------------------------
         */

        // 用户登录
        public const string SELECT_LOGIN = "select * from userinfo where userName=@userName";

        // 添加用户    
        public const string ADD_USER = "insert into userinfo values(@userId,@userName,@sex,@address,@age,@startingTime,@password,@departmentId,@phoneNumber)";

        //删除用户信息
        public const string DELETE_USER = "delete userinfo where userId =@userid";

        //修改用户信息       
        public const string UPDATE_USER = "update userinfo set sex=@sex,address=@address,age=@age,password=@password,departmentId=@departmentId,phoneNumber=@phoneNumber where userId=@userId";

        //查询所有用户    
        public const string ALL_USER = "select * from userInfo";

        //根据部门编号查询用户姓名
        public const string ALL_DEPARETMENTID_USER = "select * from userInfo where departmentid =@departmentid";

        //分页显示数据
        public const string ALL_USERS_PAGECOUNT = "select top 13 * from userInfo where id not in(select top ((@pagecount-1)*13) id from userinfo) order by id asc";

        /*
        *-----------------------------------------------------------------------------------------------------
        *----------------------------------------部门信息表---------------------------------------------------
        *-----------------------------------------------------------------------------------------------------
        */

        //查询所有部门信息
        public const string ALL_DEPARTMENT = "select * from departmentinfo";

        //根据部门编号查询部门的详细信息
        public const string DETAIL_DEPARTMETN = "select * from departmentinfo where departmentId=@departmentId";

        //添加部门信息
        public const string ADD_DEPARTMENT = "insert into departmentinfo values(@departmentId,@departmentName,@departmentnote)";

        //修改部门信息
        public const string UPDATE_DEPARTMENT = "update departmentinfo set departmentName=@departmentName,departmentnote=@departmentnote where departmentId =@departmentId";

        /*
       *------------------------------------------------------------------------------------------------------
       *----------------------------------------公告信息表----------------------------------------------------
       *------------------------------------------------------------------------------------------------------
       */
        //添加公告信息
        public const string Add_MESSAGE = "insert into messageinfo values(@messageTitle,@messageContent,@fromUserId,@publishDate)";

        //删除公告信息
        public const string DELETE_MESSAGE = "delete messageinfo where messageId=@messageId";

        //修改公告信息
        public const string UPDATE_MESSAGE = "update messageinfo set messagetitle=@messagetitle,messagecontent=@messagecontent where messageid =@messageid";

        //分页查询所有公告信息
        public const string ALL_MESSAGE_PAGE = "select top 11 messageid,messagetitle,messagecontent,fromuserid,username,d.departmentid,d.departmentname,publishdate  from messageinfo m inner join userinfo u on m.fromuserid = u.userid inner join departmentinfo d on u.departmentid = d.departmentid where messageid not in (select top ((@pagecount-1)*11) messageid from messageinfo m inner join userinfo u on m.fromuserid = u.userid inner join departmentinfo d on u.departmentid = d.departmentid order by publishdate desc) order by publishdate desc";

        //查询最新动态的头五条公告信息
        public const string GET_MESSAGE_TOINDEX = "select  top 5 *  from messageinfo order by publishDate desc";

        //查询公告信息的详细信息
        public const string ALL_MESSAGE_DETAIL = "select messageid,messagetitle,messagecontent,fromuserid,username,d.departmentid,d.departmentname,publishdate  from messageinfo m inner join userinfo u on m.fromuserid = u.userid inner join departmentinfo d on u.departmentid = d.departmentid where messageid = @messageid";

        //查询所有公告信息
        public const string ALL_MESSAGE_PAGE_COUNT = "select count(*) from  messageinfo";

        //根据用户编号查询公告信息
        public const string GETALL_MESSAGE_FROMUSERID = "select * from messageinfo where fromUserId =@fromuserid";

        //根据用户编号删除公告信息
        public const string DELETE_MESSAGE_FROMUSERID = "delete messageinfo where  fromuserid =@fromuserid";

        /*
         *-----------------------------------------------------------------------------------------------------
         *----------------------------------------公告访问信息表-----------------------------------------------
         *-----------------------------------------------------------------------------------------------------
         */

        //查询公告信息的访问人数
        public const string ALL_GETMSCALLERINFO = "select top 11 mscallerid,m.messageid,m.userid,username,callertime from mscallerinfo m inner join messageinfo ms on m.messageid = ms.messageid inner join userInfo u on m.userid= u.userid where m.messageid=@messageid and mscallerid not in (select top ((@pagecount-1)*11) mscallerid from mscallerinfo m inner join messageinfo ms on m.messageid = ms.messageid inner join userInfo u on m.userid= u.userid where m.messageid=@messageid order by callertime desc) order by callertime desc";

        //查询当前用户有没有查阅公告信息
        public const string ALL_GETMSCALLERINFO_MESSAGEID = "select * from mscallerinfo where messageid = @messageid and userid = @userid";

        //添加公告访问信息
        public const string ADD_MSCALLERINFO = "insert into mscallerinfo values(@messageid,@userid,@callertime)";

        //查询当前公告信息访问人数的总量
        public const string ALL_GETMSCALLERINFO_COUNT = "select count(*) from mscallerinfo where messageid = @messageid";
        
        /*
        *-----------------------------------------------------------------------------------------------------
        *----------------------------------------工作任务信息表-----------------------------------------------
        *-----------------------------------------------------------------------------------------------------
        */

        //查询所有工作信息状态
        public const string ALL_WORKINFO = "select * from workInfo";

        //查询自己当前接收的任务
        public const string GET_WORKINFO_BY_USERID = "select w.workid,w.title,w.workcontent,u.username,d.departmentid,d.departmentname,publishdate,status,updatedate,starttime,endtime from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid =@touserid and status <> 4 order by publishdate desc";

        //查询自己当前发布的任务
        public const string GET_WORKINFO_BY_RELEASEWORK = "select top 11 w.workid,w.title,w.workcontent,u.username,d.departmentid,d.departmentname,publishdate,status,updatedate,starttime,endtime from userInfo u inner join workInfo w on u.userid = w.touserid inner join departmentinfo d on u.departmentid = d.departmentid where w.fromuserid =@fromuserid and w.workid not in (select top ((@pagecount-1)*11) w.workid from userInfo u inner join workInfo w on u.userid = w.touserid inner join departmentinfo d on u.departmentid = d.departmentid where w.fromuserid =@fromuserid order by updatedate desc) order by updatedate desc";

        //工作信息详细
        public const string GET_WORKINFO_BY_WORKID = "select * from workInfo where workId=@workId";

        //发布工作信息
        public const string ADD_WORKINFO = "insert into workInfo values(@title,@workContent,@fromUserId,@toUserId,@publishDate,@status,@updateDate,@starttime,@endtime,null,@accessories)";

        //查询正在进行工作任务的信息
        public const string ALL_WORKINFO_JINXING = "select top 11 w.workid,w.title,w.workcontent,u.username,d.departmentid,d.departmentname,publishdate,status,updatedate,starttime,endtime from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid = @touserid and w.workid not in (select top ((@pagecount-1)*11) workid from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid = @touserid and status = 3 and getdate() < endtime) and status = 3 and getdate() < endtime";

        //查询已完成工作任务的信息
        public const string ALL_WORKINFO_WANCHENG = "select top 11 w.workid,w.title,w.workcontent,u.username,d.departmentid,d.departmentname,publishdate,status,updatedate,starttime,endtime from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid = @touserid and w.workid not in (select top ((@pagecount-1)*11) workid from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid = @touserid  and status = 4 order by publishdate desc)  and status = 4 order by publishdate desc";

        //修改状态值为5或者结束时间小于当前时间
        public const string UPDATE_WORKINFO_STATUS = "update workInfo set status = 5 where touserid = @touserid and status <> 4 and getdate() > endtime ";

        //查询已超期工作任务的信息
        public const string ALL_WORKINFO_CHAOQI = "select top 11 w.workid,w.title,w.workcontent,u.username,d.departmentid,d.departmentname,publishdate,status,updatedate,starttime,endtime from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid = @touserid and w.workid not in (select top ((@pagecount-1)*11) workid from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid = @touserid and status <> 4 and getdate() > endtime) and status <> 4 and getdate() > endtime ";

        //查询自己当前接收任务的详细信息
        public const string GET_WORKINFO_BY_USERID_DETAIL = "select w.workid,w.title,w.workcontent,u.username,d.departmentid,d.departmentname,publishdate,status,updatedate,starttime,endtime,workinfonote from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid = @touserid  and workid = @workid";

        //查询自己当前发布任务的详细信息
        public const string GET_WORKINFO_BY_RELEASEWORK_DETAIL = "select w.workid,w.title,w.workcontent,u.userid,u.username,d.departmentid,d.departmentname,publishdate,status,updatedate,starttime,endtime from userInfo u inner join workInfo w on u.userid = w.touserid inner join departmentinfo d on u.departmentid = d.departmentid where w.fromuserid = @fromuserid and workid = @workid";

        //修改工作任务的状态
        public const string UPDATE_WORK_STATUS = "update workInfo set status = @status,updatedate=@updatedate,workinfonote=@workinfonote where workid = @workid";

        //进行的工作修改为已完成然后添加完成任务的备注信息
        public const string UPDATE_WORK_STATUS_AND_WORKINFONOTE = "update workInfo set updatedate=@updatedate,status = @status,workinfonote=@workinfonote where workid = @workid";

        //修改发布的工作信息
        public const string UPDATE_WORK = "update workInfo set title=@title,workContent=@workContent,touserId=@toUserId,starttime=@starttime,endtime=@endtime where workId=@workId";

        //删除当前工作记录
        public const string DELETE_WORK = "delete workInfo where workId=@workId";

        //根据用户编号查询发布信息
        public const string GETALL_WORK_FROMUSERID = "select * from workInfo where fromuserid =@fromuserid or touserid=@fromuserid";

        //根据用户编号删除发布信息
        public const string DELETE_WORK_FROMUSERID = "delete workInfo where  fromuserid =@fromuserid";

        //分页显示自己当前接收的任务
        public const string GET_WORKINFO_BY_USERID_PAGECOUNT = "select top 11 w.workid,w.title,w.workcontent,u.username,d.departmentid,d.departmentname,publishdate,status,updatedate,starttime,endtime from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid =@touserid and status <> 4 and w.workid not in (select top ((@pagecount-1)*11) workid from workInfo where touserid =@touserid and status <> 4 order by publishdate desc) order by publishdate desc";

        //查询自己当前接收的任务所有的条数记录
        public const string GET_WORKINFO_BY_USERID_COUNT = "select count(*) from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid =@touserid and status <> 4 ";

        //查询自己当前发布的任务所有的条数记录
        public const string GET_WORKINFO_BY_RELEASEWORK_COUNT = "select  count(*) from workInfo where fromuserid =@fromuserid";

        //查询正在进行工作记录的总条数
        public const string ALL_WORKINFO_JINXING_COUNT = "select count(*)  from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid = @touserid and status = 3 and getdate() < endtime";

        //查询已完成工作任务的信息的总条数
        public const string ALL_WORKINFO_WANCHENG_COUNT = "select count(*) from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid = @touserid and status = 4 ";

        //查询已超期工作任务的信息的总条数
        public const string ALL_WORKINFO_CHAOQI_COUNT = "select count(*) from userInfo u inner join workInfo w on u.userid = w.fromuserid inner join departmentinfo d on u.departmentid = d.departmentid where w.touserid = '001' and status <> 4 and getdate() > endtime";

        /*
         *-----------------------------------------------------------------------------------------------------
         *----------------------------------------个人工作记录信息表-------------------------------------------
         *-----------------------------------------------------------------------------------------------------
         */

        //添加个人工作记录信息
        public const string ADD_WORKBLOG = "insert into WorkBloginfo values(@workblogTitle,@workblogContent,@userid,@starTime,@note)";

        //根据编号删除个人工作记录的详细信息；
        public const string DELETE_WORKBLOG = "delete WorkBloginfo where workblogid=@workblogId";

        //修改个人工作记录
        public const string UPDATE_WORKBLOG = "update WorkBloginfo set workblogTitle=@workblogTitle,workblogContent=@workblogContent,note=@note where workblogid=@workblogid";

        //根据当前用户查询所有工作记录信息
        public const string GET_WORKBLOG_USERID = "select top 11 * from WorkBloginfo where userid =@userid and workblogid not in  (select top ((@pagecount-1)*11) workblogid  from WorkBloginfo where userid =@userid order by startime desc) order by startime desc ";

        //根据工作记录编号查询工作的详细信息
        public const string GETWORKBLOG_BYID = "select * from WorkBloginfo where workblogid=@workblogid";

        //查询当前用户的消息记录条数
        public const string GETALL_WORKBLOGCOUNT_BYUSERID = "select count(*) as cun from WorkBloginfo where userid=@userid";       

        /*
       *-----------------------------------------------------------------------------------------------------
       *--------------------------------------------商品品牌表-----------------------------------------------
       *-----------------------------------------------------------------------------------------------------
       */
        //分页查询所有商品品牌信息
        public const string ALL_GETECS_BRANDINFO_PAGE = "select top 11 * from ecs_brandinfo ecs where ecs.brand_id not in (select top ((@pagecount-1)*11) brand_id from ecs_brandinfo) order by sort_order asc";
       
        //查询所有商品品牌信息
        public const string ALL_ECS_BRANDINFO = "select * from ecs_brandinfo";
       
        //添加商品品牌
        public const string ADD_ECS_BRANDINFO = "insert into ecs_brandinfo values(@brand_name,@brand_logo,@brand_desc,@site_url,@sort_order,@is_show)";
       
        //修改商品品牌信息       
        public const string UPDATE_ECS_BRANDINFO = "update ecs_brandinfo set brand_name=@brand_name,brand_logo=@brand_logo,brand_desc=@brand_desc,site_url=@site_url,sort_order=@sort_order,is_show=@is_show where brand_id=@brand_id";
      
        //删除商品品牌信息       
        public const string DELETE_ECS_BRANDINFO = "delete ecs_brandinfo where brand_id=@brand_id";
      
        //修改商品的显示状态
        public const string UPDATE_ECS_BRAND_IS_SHOW = "update ecs_brandinfo set is_show=@is_show where brand_id=@brand_id";

        /*
         *-----------------------------------------------------------------------------------------------------
         *--------------------------------------------商品类型表-----------------------------------------------
         *-----------------------------------------------------------------------------------------------------
        */
        //分页查询所有商品类型
        public const string ALL_ECS_GOODS_TYPEINFO_PAGE = "select top 11 * from ecs_goods_typeinfo where cat_id not in (select top ((@pagecount-1)*11) cat_id from ecs_goods_typeinfo)";
      
        //查询所有商品类型
        public const string ALL_ECS_GOODS_TYPEIFO = "select * from ecs_goods_typeinfo";
       
        //添加商品品牌
        public const string ADD_ECS_GOODS_TYPEINFO = "insert into ecs_goods_typeinfo values(@cat_name,@cat_enabled,@attr_group)";
       
        //根据商品类型编号查询商品类型信息
        public const string DETAIL_ECS_GOODS_TYPEINFO = "select * from ecs_goods_typeinfo where cat_id = @cat_id";
       
        //修改商品类型
        public const string UPDATE_ECS_GOODS_TYPEINFO = "update ecs_goods_typeinfo set cat_name=@cat_name,attr_group=@attr_group where cat_id=@cat_id";
       
        //删除商品类型
        public const string DELETE_ECS_GOODS_TYPEINFO = "delete ecs_goods_typeinfo where cat_id=@cat_id";
       
        //查询所有的商品类型
        public const string ALL_ECS_GOODS_TYPEINFO_CAT_NAME = "select * from ecs_goods_typeinfo";
        /*
        *-----------------------------------------------------------------------------------------------------
        *--------------------------------------------商品类型属性表-------------------------------------------
        *-----------------------------------------------------------------------------------------------------
        */
        //分页用商品类型编号查询商品类型的属性
        public const string ALL_ECS_ATTRIBUTEINFO_PAGE = "select top 11 ea.attr_id,ea.attr_name,eg.cat_id,eg.cat_name,ea.attr_input_type,ea.attr_values,ea.sort_order from  ecs_goods_typeinfo eg inner join ecs_attributeinfo ea on eg.cat_id= ea.cat_id where eg.cat_id=@cat_id and ea.attr_id not in (select top ((@pagecount-1)*11) attr_id from ecs_goods_typeinfo eg inner join ecs_attributeinfo ea on eg.cat_id= ea.cat_id where eg.cat_id=@cat_id order by sort_order desc) order by sort_order desc";
       
        //用商品类型编号查询商品类型的属性       
        public const string ALL_ECS_ATTRIBUTEINFO = "select ea.attr_id,ea.attr_name,eg.cat_id,eg.cat_name,ea.attr_input_type,ea.attr_values,ea.sort_order from  ecs_goods_typeinfo eg inner join ecs_attributeinfo ea on eg.cat_id= ea.cat_id where eg.cat_id=@new_cat_id order by sort_order desc";
       
        //添加商品类型属性       
        public const string ADD_ECS_ATTRIBUTEINFO = "insert into ecs_attributeinfo values(@cat_id,@attr_name,@attr_input_type,@attr_type,@attr_values,@attr_index,@sort_order,@is_linked,@attr_group)";
        
        //查询商品类型属性的详细信息        
        public const string DETAIL_ECS_ATTRIBUTEINFO = "select * from ecs_attributeinfo where attr_id=@attr_id";
        //修改商品类型属性       
        public const string UPDATE_ECS_ATTRIBUTEINFO = "update ecs_attributeinfo set cat_id=@cat_id,attr_name=@attr_name,attr_input_type=@attr_input_type,attr_type=@attr_type,attr_values=@attr_values,attr_index=@attr_index,is_linked=@is_linked,attr_group=@attr_group where attr_id = @attr_id";
        //删除商品类型属性
       
        public const string DELETE_ECS_ATTRIBUTEINFO = "delete ecs_attributeinfo where attr_id=@attr_id";
        //根据商品类型编号查询该商品类型的属性的总数
        public const string ALL_ECS_ATTRIBUTEINFO_CAT_ID_COUNT = "select count(*) from ecs_attributeinfo where cat_id = @cat_id";

        /*
       *-----------------------------------------------------------------------------------------------------
       *--------------------------------------------商品分类信息表-------------------------------------------
       *-----------------------------------------------------------------------------------------------------
       */
        //分页查询商品分类信息
        public const string ALL_ECS_CATEGORYINFO_PAGE = "select top 11 * from ecs_categoryinfo where cat_id not in (select top ((@pagecount-1)*11) cat_id from ecs_categoryinfo)";
       
        //查询所有商品分类信息
        public const string ALL_ECS_CATEGORYINFO = "select * from ecs_categoryinfo";
       
        //添加商品分类信息
        public const string ADD_ECS_CATEGORYINFO = "insert into ecs_categoryinfo values (@cat_name,@keywords,@cat_desc,@parent_id,@sort_order,@template_file,@measure_unit,@show_in_nav,@style,@is_show,@grade,@filter_attr)";
       
        //修改商品分类信息
        public const string UPDATE_ECS_CATEGORYINFO = "update ecs_categoryinfo set cat_name=@cat_name,keywords=@keywords,cat_desc=@cat_desc,measure_unit=@measure_unit,show_in_nav=@show_in_nav,style=@style,is_show=@is_show,grade=@grade,filter_attr=@filter_attr where cat_id=@cat_id";
        
        //删除商品分类信息
        public const string DELETE_ECS_CATEGORYINFO = "delete ecs_categoryinfo where cat_id=@cat_id";
        
        //根据商品分类编号查询商品数量
        public const string ALL_ECS_CATEGORY_CAT_ID_GOODS_COUNT = "select count(*) from ecs_goodsinfo where cat_id = @cat_id";
       
        //转移商品
        public const string UPDATE_ECS_CATEGORYINFO_ZHUANYISHANGPING = "update ecs_goodsinfo set cat_id=@new_cat_id where cat_id=@cat_id";
        
        //根据商品分类编号修改商品导航栏的状态
        public const string UPDATE_ECS_CATEGORY_SHOW_IN_NAV = "update ecs_categoryinfo set show_in_nav=@show_in_nav where cat_id=@cat_id";
        
        //根据商品分类编号修改商品是否显示的状态
        public const string UPDATE_ECS_CATEGORY_IS_SHOW = "update ecs_categoryinfo set is_show=@is_show where cat_id=@cat_id";

        /*
         *-----------------------------------------------------------------------------------------------------
         *--------------------------------------------商品信息表-----------------------------------------------
         *-----------------------------------------------------------------------------------------------------
         */

        //添加商品信息
        public const string ADD_ECS_GOODS = "insert into ecs_goodsinfo values(@cat_id,@goods_sn,@goods_name,@brand_id,@goods_number,@market_price,@warn_number,@keywords,@goods_brief,@goods_desc,@goods_img,@is_on_sale,@is_alone_sale,@add_time,@is_delete,@is_best,@is_new,@is_hot,@goods_type,@seller_note)";

        //删除商品信息
        public const string DELETE_ECS_GOODS = "update ecs_goodsinfo set is_delete=1 where goods_id=@goods_id";

        //修改商品信息
        public const string UPDATE_ECS_GOODS = "update ecs_goodsinfo set cat_id=@cat_id,goods_sn=@goods_sn,goods_name=@goods_name,brand_id=@brand_id,goods_number=@goods_number,market_price=@market_price,warn_number=@warn_number,keywords=@keywords,goods_brief=@goods_brief,goods_desc=@goods_desc,goods_img=@goods_img,is_on_sale=@is_on_sale,is_alone_sale=@is_alone_sale,is_best=@is_best,is_new=@is_new,is_hot=@is_hot,goods_type=@goods_type,seller_note=@seller_note where goods_id=@goods_id";

        //根据商品分类编号查询商品
        public const string ALL_ECS_GOODSINFO_CATEGORY_CAT_ID_PAGE = "select top 11 goods_id,goods_type,goods_name,goods_sn,market_price,goods_number,add_time from ecs_goodsinfo where cat_id=@cat_id and is_delete=0  and goods_id not in (select top ((@pagecount-1)*11) goods_id from ecs_goodsinfo where cat_id=@cat_id and is_delete=0)";

        //分页查询所有商品信息
        public const string ALL_ECS_GOODSINFO_PAGE = "select top 11 goods_id,goods_type,goods_name,goods_sn,market_price,goods_number,add_time from ecs_goodsinfo where is_delete=0  and goods_id not in (select top ((@pagecount-1)*11) goods_id from ecs_goodsinfo where is_delete=0)";

        //查询所有商品信息
        public const string ALL_ECS_GOODSINFO = "select goods_id,goods_type,goods_name,goods_sn,market_price,goods_number,add_time from ecs_goodsinfo";

        //查询商品详细信息
        public const string DETAIL_ECS_GOODS = "select * from ecs_goodsinfo where goods_id=@goods_id";

        //根据商品分类编号查询商品的所有记录的条数
        public const string ALL_ECS_GOODSINFO_CATEGORY_CAT_ID_PAGE_COUNT = "select  count(*) from ecs_goodsinfo where cat_id=@cat_id and is_delete=0";

        //查询商品信息没有被删除的记录条数
        public const string ALL_ECS_GOODSINFO_PAGE_COUNT = "select  count(*) from ecs_goodsinfo where is_delete=0";

        //分页显示所有假删除的商品信息
        public const string ALL_ECS_GOODSINFO_REMOVE_PAGE = "select top 11 goods_id,goods_name,goods_sn,market_price,goods_number from ecs_goodsinfo where is_delete=1  and goods_id not in (select top ((@pagecount-1)*11) goods_id from ecs_goodsinfo where is_delete=1)";

        //查询商品被删除的记录条数
        public const string ALL_ECS_GOODSINFO_REMOVE_PAGE_COUNT = "select count(*) from ecs_goodsinfo where is_delete=1";

        //还原商品
        public const string UPDATE_ECS_GOODSINFO_REMOVE = "update ecs_goodsinfo set is_delete=0 where goods_id=@goods_id";

        //删除商品信息
        public const string DELETE_ECS_GOODS_REMOVE = "delete ecs_goodsinfo where goods_id=@goods_id";

        //修改商品库存的数量
        public const string UPDATE_ECS_GOODS_NUMBER = "update ecs_goodsinfo set goods_number = goods_number-@goods_number where goods_id=@goods_id";

        //搜索
        //根据品牌编号、商品名称查询的商品信息
        public const string SEARCH_ALL_ECS_GOODSINFO_BRAND_ID_AND_GOODS_NAME_PAGE = "select top 11 goods_id,goods_type,brand_id,goods_name,goods_sn,market_price,goods_number,add_time from ecs_goodsinfo where is_delete=0 and brand_id = @brand_id and goods_name like @goods_name  and goods_id not in (select top ((@pagecount-1)*11) goods_id from ecs_goodsinfo where is_delete=0 and brand_id = @brand_id and goods_name like @goods_name)";

        //根据商品的添加时间查询的商品信息
        public const string SEARCH_ALL_ECS_GOODSINFO_ADD_TIME_PAGE = "select top 11 goods_id,goods_type,brand_id,goods_name,goods_sn,market_price,goods_number,add_time from ecs_goodsinfo where is_delete=0 and add_time >= @begin_time and add_time <= @end_time and goods_id not in (select top ((@pagecount-1)*11) goods_id from ecs_goodsinfo where is_delete=0 and add_time >= @begin_time and add_time <= @end_time)";

        //根据品牌编号、商品名称以及添加时间查询的商品信息
        public const string SEARCH_ALL_ECS_GOODSINFO_BRAND_ID_AND_GOODS_NAME_AND_ADD_TIME_PAGE = "select top 11 goods_id,goods_type,brand_id,goods_name,goods_sn,market_price,goods_number,add_time from ecs_goodsinfo where is_delete=0 and brand_id = @brand_id and goods_name like @goods_name and add_time >= @begin_time and add_time <= @end_time  and goods_id not in (select top ((@pagecount-1)*11) goods_id from ecs_goodsinfo where is_delete=0 and brand_id = @brand_id and goods_name like @goods_name and add_time >= @begin_time and add_time <= @end_time)";

        //根据品牌编号、商品名称的总共数据记录
        public const string SEARCH_ALL_ECS_GOODSINFO_PAGE_COUNT = "select count(*) from ecs_goodsinfo where is_delete=0 and brand_id = @brand_id and goods_name like @goods_name";

        //根据商品的添加时间查询的总共数据记录
        public const string SEARCH_ALL_ECS_GOODSINFO_ADD_TIME_PAGE_COUNT = "select count(*) from ecs_goodsinfo where is_delete=0 and add_time >= @begin_time and add_time <= @end_time";

        //根据品牌编号、商品名称以及添加时间查询的总共数据记录
        public const string SEARCH_ALL_ECS_GOODSINFO_BRAND_ID_AND_GOODS_NAME_AND_ADD_TIME_PAGE_count = "select count(*) from ecs_goodsinfo where is_delete=0 and brand_id = @brand_id and goods_name like @goods_name and add_time >= @begin_time and add_time <= @end_time";

        /*
        *-----------------------------------------------------------------------------------------------------
        *--------------------------------------------商品信息属性表-------------------------------------------
        *-----------------------------------------------------------------------------------------------------
        */
        //根据商品编号查询商品属性
        public const string ALL_ECS_GOODS_ATTRINFO = "select * from ecs_goods_attrinfo where goods_id = @goods_id";

        //添加商品属性
        public const string ADD_ECS_GOODS_ATTRIFO = "insert into ecs_goods_attrinfo values(@goods_id,@attr_id,@attr_value,@attr_price)";

        //修改商品属性
        public const string UPDATE_ECS_GOODS_ATTRINFO = "update ecs_goods_attrinfo set attr_value=@attr_value,attr_price=@attr_price where goods_id =@goods_id and attr_id=@attr_id";

        public const string DELETE_ECS_GOODS_ATTRINFO = "delete ecs_goods_attrinfo where goods_id=@goods_id";
        
        /*
         *-----------------------------------------------------------------------------------------------------
         *--------------------------------------------商品发货信息表-------------------------------------------
         *-----------------------------------------------------------------------------------------------------
       */
        //添加商品发货信息
        public const string ADD_ECS_SELLSTOCKINFO = "insert into ecs_sellstockinfo values(@sell_goods_id,@sell_goods_name,@sell_goods_sn,@sell_goods_type,@sell_brand_id,@sell_order_number,@sell_Delivery_number,@sell_consignment_mode,@sell_consignment_web,@sell_attr_values,@sell_number,@sell_price,@sell_end_time,@sell_remark)";

        //查询所有发货的商品信息
        public const string ALL_ECS_SELLSTOCKINFO = "select  * from ecs_sellstockinfo";

        //分页查询发送商品的货物
        public const string ALL_ECS_SELLSTOCKINFO_PAGE = "select top 11 * from ecs_sellstockinfo where sell_id not in (select top ((@pagecount-1)*11) sell_id from ecs_sellstockinfo order by sell_end_time desc) order by sell_end_time desc";

        //查询发货商品总共记录数
        public const string ALL_ECS_SELLSTOCKINFO_PAGE_COUNT = "select count(*) from ecs_sellstockinfo";

        //搜索
        //根据商品名称得到的发货记录信息  
        public const string ALL_ECS_SELLSTOCKINFO_AND_SELL_GOODS_NAME_PAGE = "select top 11 * from ecs_sellstockinfo where sell_goods_name like @sell_goods_name and sell_id not in (select top ((@pagecount-1)*11) sell_id from ecs_sellstockinfo where sell_goods_name like @sell_goods_name order by sell_end_time desc) order by sell_end_time desc";

        //根据发货时间得到的发货记录信息
        public const string ALL_ECS_SELLSTOCKINFO_AND_SELL_END_TIME_PAGE = "select top 11 * from ecs_sellstockinfo where sell_end_time >= @begin_time and sell_end_time <= @end_time and sell_id not in (select top ((@pagecount-1)*11) sell_id from ecs_sellstockinfo where sell_end_time >= @begin_time and sell_end_time <= @end_time order by sell_end_time desc) order by sell_end_time desc";

        //根据商品名称、发货时间得到的发货记录信息       
        public const string ALL_ECS_SELLSTOCKINFO_AND_SELL_GOODS_NAME_NAD_SELL_END_TIME_PAGE = "select top 11 * from ecs_sellstockinfo where sell_goods_name like @sell_goods_name and (sell_end_time >= @begin_time and sell_end_time <= @end_time) and sell_id not in (select top ((@pagecount-1)*11) sell_id from ecs_sellstockinfo where sell_goods_name like @sell_goods_name and (sell_end_time >= @begin_time or sell_end_time <= @end_time) order by sell_end_time desc) order by sell_end_time desc";

        //搜索得到的总数
        //根据商品名称得到的发货记录信息的总数
        public const string ALL_ECS_SELLSTOCKINFO_AND_SELL_GOODS_NAME_PAGE_COUNT = "select count(*) from ecs_sellstockinfo where sell_goods_name like @sell_goods_name";

        //根据发货时间得到的发货记录信息的总数
        public const string ALL_ECS_SELLSTOCKINFO_AND_SELL_END_TIME_PAGE_COUNT = "select count(*) from ecs_sellstockinfo where sell_end_time >= @begin_time and sell_end_time <= @end_time";

        //根据商品名称、发货时间得到的发货记录信息的总数
        public const string ALL_ECS_SELLSTOCKINFO_AND_SELL_GOODS_NAME_NAD_SELL_END_TIME_PAGE_COUNT = "select count(*) from ecs_sellstockinfo where sell_goods_name like @sell_goods_name and sell_end_time >= @begin_time and sell_end_time <= @end_time";


        /*
        *-----------------------------------------------------------------------------------------------------
        *--------------------------------------------订单信息表-----------------------------------------------
        *-----------------------------------------------------------------------------------------------------
      */
        //分页查询商品的订单信息
        public const string ALL_ECS_ORDER_INFO_PAGE = "select top 11 * from ecs_order_info where id not in (select top((@pagecount-1)*11) id from ecs_order_info order by order_sn desc) order by order_sn desc";

    }
}